package com.cgc.venus.server.user.service;

import com.cgc.venus.server.core.base.AbstractBaseService;
import com.cgc.venus.server.core.bean.page.PageQuery;
import com.cgc.venus.server.core.exception.VenusException;
import com.cgc.venus.server.core.k8s.inter.K8sManageService;
import com.cgc.venus.server.core.utils.MD5Common;
import com.cgc.venus.server.user.bean.UserBean;
import com.cgc.venus.server.user.inter.UserService;
import io.fabric8.kubernetes.api.model.Pod;
import io.fabric8.kubernetes.api.model.PodList;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by xyr on 2017/9/4.
 * 用户管理模块dao实现层
 */

@Service
public class UserServiceImpl extends AbstractBaseService implements UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private K8sManageService k8sManageService;

    /*
    * 根据用户账号和登录密码,得到用户的ID
    * */
    @Override
    public String getUserId(String loginName,String loginPassword) throws VenusException {
        String sql = "SELECT id FROM t_user WHERE login_name = ? AND login_password = ?";
        List<Object> objects = new ArrayList<Object>();
        objects.add(loginName);
        // 将密码加密
        loginPassword = MD5Common.encryptMD5(loginPassword);
        objects.add(loginPassword);
        Map<String, Object> map = null;
        try {
            map = jdbcTemplate.queryForMap(sql, objects.toArray());
        } catch (EmptyResultDataAccessException ex) {
            return null;
        }
        if (map.size() == 0 || map == null) return null;
        String userId = (String) map.get("id");
        return userId;
    }

    /*
    * 登记用户登录信息
    * */
    @Override
    public int register(String loginName,String token) throws VenusException {
        String sql = "UPDATE t_user user SET user.token = ?, user.login_time = NOW() WHERE user.login_name=? ";
        List<Object> objects = new ArrayList<Object>();
        objects.add(token);
        objects.add(loginName);
        int row = jdbcTemplate.update(sql,objects.toArray());
        return row;
    }

    @Override
    public int logout(String token) throws VenusException {
        String sql = "UPDATE t_user user SET user.token = '', user.login_time = NULL WHERE `user`.token = ? ";
        List<Object> objects = new ArrayList<Object>();
        objects.add(token);
        int row = jdbcTemplate.update(sql,objects.toArray());
        return row;
    }

    /*
    * token验证
    * return token 登录token
    * return login_time 登录时间
    * return session_expire_time session失效时间
    * */
    @Override
    public Map<String, Object> validateToken(String token) throws VenusException {
        String sql = "SELECT `user`.token,`user`.login_time, `user`.session_expire_time FROM t_user user WHERE `user`.token = ? ";
        List<Object> objects = new ArrayList<Object>();
        objects.add(token);
        Map<String, Object> map = new HashMap<String, Object>();
        try {
            map = jdbcTemplate.queryForMap(sql, objects.toArray());
        } catch (EmptyResultDataAccessException ex) {
            return map;
        }
        return map;
    }

    /*
    * 登录账号验证
    * */
    @Override
    public int validateAccount(String loginName) throws VenusException {
        String sql = "SELECT id FROM t_user WHERE login_name = ? ";
        List<Object> objects = new ArrayList<Object>();
        objects.add(loginName);
        Map<String, Object> map = null;
        try {
            map = jdbcTemplate.queryForMap(sql, objects.toArray());
        } catch (EmptyResultDataAccessException ex) {
            return 0;
        }
        if (map.size() == 0 || map == null) return 0;
        return 1;
    }

    /*
    * 新增用户
    * */
    @Override
    public int addUser(UserBean userBean) throws VenusException {
        String sql = "INSERT INTO t_user (id,login_name,login_password,name," +
                " sex,phone,email,role,session_expire_time,create_time,last_update_time) " +
                " VALUES(?,?,?,?,?,?,?,?,?,NOW(),NOW()) ";
        List<Object> objects = new ArrayList<Object>();
        objects.add(userBean.getId());
        objects.add(userBean.getLoginName());
        objects.add(MD5Common.encryptMD5(userBean.getLoginPassword()));
        objects.add(userBean.getName());
        objects.add(userBean.getSex());
        objects.add(userBean.getPhone());
        objects.add(userBean.getEmail());
        objects.add(userBean.getRole());
        objects.add(userBean.getSessionExpireTime());
        int row = jdbcTemplate.update(sql,objects.toArray());
        return row;
    }

    /*
    * 根据用户账号修改用户信息
    * */
    @Override
    public int updateUser(UserBean userBean) throws VenusException {
        String sql = "UPDATE t_user SET name=?, sex=?, phone=?, " +
                "email=?, role=?, session_expire_time=? WHERE id=?";
        List<Object> objects = new ArrayList<Object>();
        // objects.add(MD5Common.encryptMD5(userBean.getLoginPassword()));
        objects.add(userBean.getName());
        objects.add(userBean.getSex());
        objects.add(userBean.getPhone());
        objects.add(userBean.getEmail());
        objects.add(userBean.getRole());
        objects.add(userBean.getSessionExpireTime());
        objects.add(userBean.getId());
        int row = jdbcTemplate.update(sql,objects.toArray());
        return row;
    }

    /*
    * 分页查询,查询全部用户信息
    * */
    @Override
    public List<Map<String, Object>> queryAllUser(int page, int pageSize) throws VenusException {
        String sql = "SELECT `user`.id, `user`.login_name AS loginName, `user`.login_password AS loginPassword, " +
                " `user`.`name`, `user`.sex, `user`.phone, " +
                "  `user`.email, `role`.name AS role, role.id AS roleId, `user`.session_expire_time AS sessionExpireTime " +
                " FROM t_user user LEFT JOIN t_role role ON `user`.role=role.id " +
                " ORDER BY user.create_time DESC  LIMIT ?,?";
        List<Object> objects = new ArrayList<Object>();
        page = (page-1) * pageSize;
        objects.add(page);
        objects.add(pageSize);
        List<Map<String,Object>> maps = new ArrayList<Map<String, Object>>();
        try{
            maps = jdbcTemplate.queryForList(sql, objects.toArray());
        }catch (EmptyResultDataAccessException ex){
            return maps;
        }
        return maps;
    }

    /*
    * 统计所有用户信息数量
    * */
    @Override
    public long countAllUser(PageQuery pageQuery) throws VenusException {
        long count = 0;
        Map<String,Object> map = null;
        String inputContent = pageQuery.getQueryKey();
        String sql = "SELECT COUNT(*) AS count  FROM t_user";
        try {
            if (StringUtils.isNotBlank(inputContent) ){
                // 输入不为空,根据输入来查询,分组查询
                sql = "SELECT COUNT(*) AS count  FROM t_user WHERE login_name LIKE ? OR name LIKE ? ";
                List<Object> objectList = new ArrayList<Object>();
                objectList.add("%" + inputContent+ "%");
                objectList.add("%" + inputContent+ "%");
                map = jdbcTemplate.queryForMap(sql,objectList.toArray());
            } else {
                map = jdbcTemplate.queryForMap(sql);
            }
        } catch (EmptyResultDataAccessException EX){
            return count;
        }
        if (map.size() > 0){
            count = (Long) map.get("count");
        }
        return count;
    }

    /*
    * 分页查询,根据输入关键字,模糊查询用户信息
    * */
    @Override
    public List<Map<String, Object>> queryUserByKey(PageQuery pageQuery) throws VenusException {
        String sql = "SELECT `user`.id, `user`.login_name AS loginName, `user`.login_password AS loginPassword, " +
                "`user`.`name`, `user`.sex, `user`.phone, " +
                "  `user`.email, `role`.name AS role, role.id AS roleId, `user`.session_expire_time AS sessionExpireTime " +
                " FROM t_user user LEFT JOIN t_role role ON `user`.role=role.id WHERE `user`.login_name " +
                " LIKE ? OR user.NAME LIKE ? OR `role`.name LIKE" +
                " ? ORDER BY user.create_time DESC  LIMIT ?,?";
        List<Object> objects = new ArrayList<Object>();
        objects.add("%"+pageQuery.getQueryKey()+"%");
        objects.add("%"+pageQuery.getQueryKey()+"%");
        objects.add("%"+pageQuery.getQueryKey()+"%");
        int page = (pageQuery.getPage()-1) * pageQuery.getPageSize();
        objects.add(page);
        objects.add(pageQuery.getPageSize());
        List<Map<String,Object>> maps = new ArrayList<Map<String, Object>>();
        try{
            maps = jdbcTemplate.queryForList(sql, objects.toArray());
        }catch (EmptyResultDataAccessException ex){
            return maps;
        }
        return maps;
    }

    /*
    * 根据用户的ID,删除用户信息
    * */
    @Override
    public int deleteUserById(String userId) throws VenusException {
        String sql = "DELETE FROM t_user WHERE id=?";
        List<Object> list = new ArrayList<Object>();
        list.add(userId);
        int row = jdbcTemplate.update(sql,list.toArray());
        return  row;
    }

    /*
    * 根据登录的token,得到用户角色,功能菜单,团队信息等
    * */
    @Override
    public Map<String, Object> getUserByToken(String token) throws VenusException {
        String sql = " SELECT DISTINCT u.id AS userId,u.name AS userName, u.login_name AS loginName, u.email, u.phone, r.name AS roleName, r.function AS rolePermission," +
                " r.is_superadmin AS isSuperAdmin FROM t_user u " +
                " LEFT JOIN t_role r ON u.role = r.id WHERE u.token = ? ";

        List<Object> objectList = new ArrayList<Object>();
        objectList.add(token);

        Map<String,Object> map = new HashMap<String, Object>();

        try {
            map = jdbcTemplate.queryForMap(sql,objectList.toArray());
        } catch (EmptyResultDataAccessException EX) {
            return map;
        }
        return map;
    }

    @Override
    public Map<String, Object> getFunctionById(String id) throws VenusException {
        String sql = "SELECT url FROM t_function WHERE id = ?  AND active=0";
        List<Object> objects = new ArrayList<Object>();
        objects.add(id);
        Map<String,Object> map = new HashMap<String, Object>();
        try {
            map = jdbcTemplate.queryForMap(sql,objects.toArray());
        } catch (EmptyResultDataAccessException EX) {
            return map;
        }
        return map;
    }

    /*
    * 根据用户的ID,修改用户密码
    * */
    @Override
    public int updatePasswordById(String userId, String password) throws VenusException {
        String sql = "UPDATE t_user SET login_password=? WHERE id = ?";
        List<Object> objects = new ArrayList<Object>();
        objects.add(MD5Common.encryptMD5(password));
        objects.add(userId);
        int row = jdbcTemplate.update(sql,objects.toArray());
        return row;
    }

    /*
    * 根据登录的token,更新登录时间
    * */
    @Override
    public int updateLoginTime(String token) throws VenusException {
        String sql = "UPDATE t_user SET login_time=NOW() WHERE token = ?";
        List<Object> objects = new ArrayList<Object>();
        objects.add(token);
        int row = jdbcTemplate.update(sql,objects.toArray());
        return row;
    }

    /*
    * 获得登录用户的团队信息，根据用户ID，和是否超级管理员
    * */
    @Override
    public List<Map<String, Object>> getTeamCombobox(String userId, int isSuperAdmin) throws VenusException {
        List<Object> objs = new ArrayList<Object>();
        StringBuffer sqlBuf = new StringBuffer();
        sqlBuf.append(" SELECT DISTINCT ")
                .append(" 	tm.id, ")
                .append(" 	tm.name_cn AS nameCn, ")
                .append(" 	tm.name_en AS nameEn  ")
                .append(" FROM ")
                .append(" 	t_team tm ")
                .append(" LEFT JOIN t_namespace ns ON ns.team = tm.id ")
                .append(" LEFT JOIN t_namespace_user nsu ON nsu.namespace = ns.id ")
                .append(" WHERE ")
                .append(" 	tm.status = 1 ");
        if (isSuperAdmin == 0) {
            sqlBuf.append(" AND nsu.user=? ");
            objs.add(userId);
        }
        List<Map<String,Object>> list = new ArrayList<Map<String, Object>>();
        try {
            if (objs.size() > 0) {
                list = jdbcTemplate.queryForList(sqlBuf.toString(),objs.toArray());
            } else {
                list = jdbcTemplate.queryForList(sqlBuf.toString());
            }
        } catch (EmptyResultDataAccessException ex) {
            return list;
        }
        return  list;
    }

    /*
    * 根据团队ID，获得空间信息,加载选择下拉框
    * */
    @Override
    public List<Map<String, Object>> getNamespaceCombobox(String teamId,String userId,int isSuperAdmin) throws VenusException {
        /*String sql = "SELECT DISTINCT id, kube_name AS kubeName,IF(type = 0,'开发环境',IF(type = 1,'测试环境',IF(type = 2, '预生产环境','生产环境')))" +
                " AS typeName FROM t_namespace WHERE STATUS = 1 AND team = ? ";*/
        /*String sql = "SELECT DISTINCT id, kube_name AS kubeName,name " +
                " AS typeName FROM t_namespace WHERE STATUS = 1 AND team = ? ";*/

        List<Object> objects = new ArrayList<Object>();


        String sql = "SELECT DISTINCT n.id, n.kube_name AS kubeName,n.name " +
                "                 AS typeName FROM t_namespace n  LEFT JOIN t_namespace_user nu ON n.id = nu.namespace " +
                "                 WHERE STATUS = 1 AND n.team = ? ";
        objects.add(teamId);

        if (isSuperAdmin == 0) {
            sql = "SELECT DISTINCT n.id, n.kube_name AS kubeName,n.name " +
                    "                 AS typeName FROM t_namespace n  LEFT JOIN t_namespace_user nu ON n.id = nu.namespace " +
                    "                 WHERE STATUS = 1 AND n.team = ? AND nu.user = ? ";

            objects.add(userId);
        }


        List<Map<String,Object>> maps = new ArrayList<Map<String, Object>>();
        try {
            maps = jdbcTemplate.queryForList(sql,objects.toArray());
        } catch (EmptyResultDataAccessException EX) {
            return maps;
        }
        return maps;
    }

    /*
    * 根据用户ID,获得团队信息,删除用户时候用
    * */
    @Override
    public List<Map<String, Object>> getTeamByUserId(String userId) {
        String sql = "SELECT id FROM t_team WHERE user = ? ";
        List<Object> objects = new ArrayList<Object>();
        objects.add(userId);
        List<Map<String,Object>> maps = new ArrayList<Map<String, Object>>();
        try {
            maps = jdbcTemplate.queryForList(sql,objects.toArray());
        } catch (EmptyResultDataAccessException EX) {
            return maps;
        }
        return maps;
    }

    /*
    * 根据用户的ID,获得空间信息,删除用户时候用
    * */
    @Override
    public List<Map<String, Object>> getNamespaceByUserId(String userId) {
        String sql = "SELECT id FROM t_namespace_user  WHERE user = ? ";
        List<Object> objects = new ArrayList<Object>();
        objects.add(userId);
        List<Map<String,Object>> maps = new ArrayList<Map<String, Object>>();
        try {
            maps = jdbcTemplate.queryForList(sql,objects.toArray());
        } catch (EmptyResultDataAccessException EX) {
            return maps;
        }
        return maps;
    }

    @Override
    public List<Map<String, Object>> getNamespaceAndTeamByUserId(String userId) throws VenusException {
        String sql = "SELECT DISTINCT n.kube_name AS namespace,n.name AS namespace_cn ,team.name_cn AS team FROM " +
                "(t_namespace_user nu LEFT JOIN t_namespace n ON nu.namespace = n.id )" +
                "  LEFT JOIN t_team team ON team.id = n.team  WHERE nu.user = ? ";

        List<Object> list = new ArrayList<Object>();
        list.add(userId);

        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();

        try {
            mapList = jdbcTemplate.queryForList(sql,list.toArray());
        } catch (EmptyResultDataAccessException e) {
            return mapList;
        }
        return mapList;
    }

    @Override
    public List<Map<String, Object>> getNamespaceAndTeam() throws VenusException {
        /*String sql = "SELECT DISTINCT n.kube_name AS namespace,n.name AS namespace_cn ,team.name_cn AS team FROM " +
                "(t_namespace_user nu LEFT JOIN t_namespace n ON nu.namespace = n.id )" +
                "  LEFT JOIN t_team team ON team.id = n.team ";*/
        String sql = "SELECT DISTINCT n.kube_name AS namespace,n.name AS namespace_cn ,team.name_cn AS team FROM " +
                " t_namespace n " +
                "  LEFT JOIN t_team team ON team.id = n.team ";

        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();

        try {
            mapList = jdbcTemplate.queryForList(sql);
        } catch (EmptyResultDataAccessException e) {
            return mapList;
        }
        return mapList;
    }

    /*
    * 根据空间获取应用数量
    * */
    @Override
    public long getAppCountByNamespace(String namespace) throws VenusException {
        String sql = "SELECT COUNT(id) AS num FROM t_app_manage app WHERE app.namespace = ? ";

        List<Object> list = new ArrayList<Object>();
        list.add(namespace);
        Map<String,Object> map = new HashMap<String,Object>();

        try {
            map = jdbcTemplate.queryForMap(sql,list.toArray());

            if (map.size() > 0) {
                return (Long) map.get("num");
            }
        } catch (EmptyResultDataAccessException e) {
            return 0;
        }
        return 0;
    }

    /*
    * 根据空间,获取k8spod数量
    * */
    @Override
    public int getPodCountByNamespace(String namespace) throws VenusException {
        PodList podList = k8sManageService.getPodList(namespace);
        if (podList != null) {
            List<Pod> podList1 = podList.getItems();
            if (podList1 != null) {
                return podList1.size();
            }
        }
        return 0;
    }

    /*
   * 根据用户ID,获取团队信息
   * */
    @Override
    public List<Map<String, Object>> getTeamBaseByUserId(String userId) throws VenusException {
        String sql = "SELECT DISTINCT team.name_cn AS team FROM " +
                "(t_namespace_user nu LEFT JOIN t_namespace n ON nu.namespace = n.id )" +
                "  LEFT JOIN t_team team ON team.id = n.team  WHERE nu.user = ? ";

        List<Object> list = new ArrayList<Object>();
        list.add(userId);

        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();

        try {
            mapList = jdbcTemplate.queryForList(sql,list.toArray());
        } catch (EmptyResultDataAccessException e) {
            return mapList;
        }
        return mapList;
    }

    /*
    * 获取团队信息
    * */
    @Override
    public List<Map<String, Object>> getTeamBase() throws VenusException {
        /*String sql = "SELECT DISTINCT team.name_cn AS team FROM " +
                "(t_namespace_user nu LEFT JOIN t_namespace n ON nu.namespace = n.id )" +
                "  LEFT JOIN t_team team ON team.id = n.team   ";*/
        String sql = "SELECT team.name_cn AS team FROM t_team team";

        List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();

        try {
            mapList = jdbcTemplate.queryForList(sql);
        } catch (EmptyResultDataAccessException e) {
            return mapList;
        }
        return mapList;
    }

    /*
    * 根据团队名称,统计团队所属空间数量,或用户数量
    * */
    @Override
    public Map<String, Object> countNamespaceAndUserByTeam(String teamNameCn) throws VenusException {
        String sql = "SELECT SUM(namespace_count) namespace_count,SUM(user_count) user_count FROM ( " +
                "  SELECT COUNT(*) namespace_count , 0 user_count FROM  t_namespace n  " +
                "  LEFT JOIN t_team team ON team.id = n.team  WHERE team.name_cn = ? " +
                "  UNION ALL " +
                "  SELECT  0  namespace_count,COUNT(*) user_count FROM (t_namespace_user nu LEFT JOIN t_namespace n ON nu.namespace = n.id ) " +
                "  LEFT JOIN t_team team ON team.id = n.team  WHERE  team.name_cn = ? " +
                ") t";

        List<Object> list = new ArrayList<Object>();
        list.add(teamNameCn);
        list.add(teamNameCn);
        Map<String,Object> map = new HashMap<String,Object>();
        try {
            map = jdbcTemplate.queryForMap(sql,list.toArray());
        } catch (EmptyResultDataAccessException e) {
            return map;
        }
        return map;
    }

    @Override
    public int editPhone(String userId, String phone) throws VenusException {
        String sql = "UPDATE t_user SET phone = ? WHERE id = ? ";
        List<Object> list = new ArrayList<>();
        list.add(phone);
        list.add(userId);
        int row = jdbcTemplate.update(sql,list.toArray());
        return row;
    }

    @Override
    public int editEmail(String userId, String email) throws VenusException {
        String sql = "UPDATE t_user SET email = ? WHERE id = ? ";
        List<Object> list = new ArrayList<>();
        list.add(email);
        list.add(userId);
        int row = jdbcTemplate.update(sql,list.toArray());
        return row;
    }


}
